package vn.edu.saigontech.ncms.web.receipt;

import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import vn.edu.saigontech.ncms.model.info.Receipt;
import vn.edu.saigontech.ncms.model.info.ReceiptRefund;
import vn.edu.saigontech.ncms.service.ReceiptRefundService;
import vn.edu.saigontech.ncms.service.ReceiptService;
import vn.edu.saigontech.ncms.util.DateUtil;
import vn.edu.saigontech.ncms.util.ExcelUtil;
import vn.edu.saigontech.ncms.util.I18n;
import vn.edu.saigontech.ncms.web.BaseAction;
import vn.edu.saigontech.ncms.web.receipt.report.RefundReport;

@SuppressWarnings("serial")
public class ReceiptRefundAction extends BaseAction {
	private Long id;
	private Long receiptId;
	private String date;
	private Double fee;
	private String reason;
	private String comment;
	
	private String fromDate;
	private String toDate;
	
	public String showAdd() {
		REQUEST().setAttribute("receipt", ReceiptService.INSTANCE.findById(this.receiptId));
		return SUCCESS;
	}
	
	public String add() {
		ReceiptRefund refund = new ReceiptRefund();
		Receipt receipt = ReceiptService.INSTANCE.findById(this.receiptId);
		refund.setReceipt(receipt);
		refund.setFee(this.fee);
		refund.setReason(this.reason);
		refund.setDate(DateUtil.fromString(this.date, this.i18n("format.date")));
		ReceiptRefundService.INSTANCE.persist(refund);
		
		this.id = refund.getId();
		
		String student = receipt.getStudent().getFullName();
		String number = receipt.getNumber();
		String offering = receipt.getOffering().getName();
		
		this.addLoggingDetail(BaseAction.ADD, "logging.refund.add", student, number, offering);
		return SUCCESS;
	}
	
	public String showEdit() {
		REQUEST().setAttribute("refund", ReceiptRefundService.INSTANCE.findById(this.id));
		return SUCCESS;
	}
	
	public String edit() {
		ReceiptRefund refund = ReceiptRefundService.INSTANCE.findById(this.id);
		refund.setFee(this.fee);
		refund.setReason(this.reason);
		refund.setDate(DateUtil.fromString(this.date, this.i18n("format.date")));
		refund.setComment(this.comment);
		ReceiptRefundService.INSTANCE.persist(refund);
		
		String student = refund.getReceipt().getStudent().getFullName();
		String number = refund.getReceipt().getNumber();
		String offering = refund.getReceipt().getOffering().getName();
		
		this.addLoggingDetail(BaseAction.EDIT, "logging.refund.edit", student, number, offering);
		return SUCCESS;
	}
	
	public String delete() {
		ReceiptRefund refund = ReceiptRefundService.INSTANCE.findById(this.id);
		
		String student = refund.getReceipt().getStudent().getFullName();
		String number = refund.getReceipt().getNumber();
		String offering = refund.getReceipt().getOffering().getName();
		
		if(ReceiptRefundService.INSTANCE.deleteById(this.id)) {
			this.setKeyMessage("confirm.deleteSuccessfully", "refund");
			this.addLoggingDetail(BaseAction.DELETE, "logging.refund.delete", student, number, offering);
		}
		else {
			this.setKeyMessage("confirm.deleteUnsuccessfully", "refund");
		}		
		return SUCCESS;
	}
	
	public String showReport() {
		this.fromDate = DateUtil.toString(DateUtil.startOfMonth(DateUtil.newDate()), this.i18n("format.date"));
		this.toDate = DateUtil.toString(DateUtil.endOfMonth(DateUtil.newDate()), this.i18n("format.date"));
		return SUCCESS;
	}
	
	public String report() {
		Date fromDateDate = DateUtil.fromString(this.fromDate, this.i18n("format.date"));
		Date toDateDate = DateUtil.fromString(this.toDate, this.i18n("format.date"));
		
		RefundReport report = new RefundReport(fromDateDate, toDateDate);
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "Refund Report" + ".xls\"");
		
		try {
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("Refund Report");
			
			HSSFFont fontBold14 = ExcelUtil.makeFont(workbook, "Times New Roman", 14, true);
			HSSFFont fontBold12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, true);
			HSSFFont fontBold10 = ExcelUtil.makeFont(workbook, "Times New Roman", 10, true);
			
			HSSFFont fontNormal14 = ExcelUtil.makeFont(workbook, "Times New Roman", 14, false);
			HSSFFont fontNormal11 = ExcelUtil.makeFont(workbook, "Times New Roman", 11, false);
			HSSFFont fontNormal9 = ExcelUtil.makeFont(workbook, "Times New Roman", 9, false);
			
			HSSFCellStyle styleCenter_B14 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold14);
			HSSFCellStyle styleCenter_B12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold12);
			HSSFCellStyle styleCenter_B10 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold10);
			
			HSSFCellStyle styleCenter_N14 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal14);
			HSSFCellStyle styleCenter_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			HSSFCellStyle styleCenter_N9 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal9);
			
			HSSFCellStyle styleLeft_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			HSSFCellStyle styleRight_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			
			HSSFCellStyle styleRight_B10 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_CENTER, fontBold10);
			
			int r = 0;
			HSSFRow row = sheet.createRow(r);
			
			HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B14);
			row.setHeightInPoints(21);
			cell.setCellValue(new HSSFRichTextString("BẢNG KÊ HOÀN TRẢ TIỀN HỌC PHÍ ANH VĂN TỐI"));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(21);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N14);
			cell.setCellValue(new HSSFRichTextString("Cơ Sở: Quang Trung"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 7));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			cell.setCellValue(new HSSFRichTextString(String.format("Từ %s Đến %s", fromDate, toDate)));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 7));
			
			// BLANK
			r = r + 1;
			row = sheet.createRow(r);
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(20);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, "STT", 256*6, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, "HỌ & TÊN", 256*25, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "SỐ TIỀN", 256*12, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "LÝ DO", 256*28, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "NGÀY HOÀN TRẢ", 256*17, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "GHI CHÚ", 256*17, styleCenter_B10);
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 1, 1));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 4, 4));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 5, 5));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 6, 6));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 7, 7));
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "BIÊN LAI", 256*10, styleCenter_B10);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 2, 3));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(20);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "NGÀY", 256*10, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "SỐ", 256*16, styleCenter_B10);
			
			int firstRow = r + 2;
			
			int index = 1;
			List<ReceiptRefund> refunds = report.getRefunds();
			for(int i = 0; i < refunds.size(); i++) {
				ReceiptRefund refund = refunds.get(i);
				
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(22);
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(index);
				cell.setCellStyle(styleCenter_N11);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(refund.getReceipt().getStudent().getFullName()));
				cell.setCellStyle(styleLeft_N11);
				
				String date = DateUtil.toString(refund.getReceipt().getDate(), new I18n().getMessage("format.vndate"));
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(date));
				cell.setCellStyle(styleCenter_N11);
				
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(refund.getReceipt().getNumber()));
				cell.setCellStyle(styleCenter_N11);
				
				cell = row.createCell(4, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(refund.getReceipt().getFee().getAmount());
				cell.setCellStyle(styleRight_N11);
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				
				cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(refund.getReason()));
				cell.setCellStyle(styleLeft_N11);
				
				String returnDate = DateUtil.toString(refund.getDate(), new I18n().getMessage("format.vndate"));
				cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(returnDate));
				cell.setCellStyle(styleCenter_N11);
				
				cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(refund.getComment()));
				cell.setCellStyle(styleLeft_N11);
				
				index++;
			}
			
			int lastRow = r + 1;
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(18);
			
			cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B10);
			cell.setCellValue(new HSSFRichTextString("TỔNG CỘNG"));
			
			cell = row.createCell(4, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleRight_B10);
			if(lastRow >= firstRow) {
				cell.setCellFormula("SUM(E" + firstRow + ":"  + "E" + lastRow + ")");
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			}
			
			r = r + 2;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			
			cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Ngày"));
			cell.setCellValue(new HSSFRichTextString(String.format("Ngày %d Tháng %d Năm %d", 
					DateUtil.getDayOfMonth(toDateDate),
					DateUtil.getMonth(toDateDate) + 1,
					DateUtil.getYear(toDateDate))));
			cell.setCellStyle(styleCenter_N9);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 6, 7));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			
			cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Hiệu Trưởng"));
			cell.setCellStyle(styleCenter_N9);
			
			cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Trưởng bộ phận"));
			cell.setCellStyle(styleCenter_N9);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 4));
			
			cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Người lập"));
			cell.setCellStyle(styleCenter_N9);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 6, 7));

			r = r + 4;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			
			cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(report.getPcl()));
			cell.setCellStyle(styleCenter_N9);
			
			cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(report.getLep()));
			cell.setCellStyle(styleCenter_N9);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 4));
			
			cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(report.getRrp()));
			cell.setCellStyle(styleCenter_N9);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 6, 7));
			
			workbook.write(RESPONSE().getOutputStream());
		}catch (Exception ex) {
			ex.printStackTrace();
		}
		return NONE;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public Long getReceiptId() {
		return receiptId;
	}

	public void setReceiptId(Long receiptId) {
		this.receiptId = receiptId;
	}

	public String getDate() {
		return date;
	}

	public void setDate(String date) {
		this.date = date;
	}

	public Double getFee() {
		return fee;
	}

	public void setFee(Double fee) {
		this.fee = fee;
	}

	public String getReason() {
		return reason;
	}

	public void setReason(String reason) {
		this.reason = reason;
	}

	public String getComment() {
		return comment;
	}

	public void setComment(String comment) {
		this.comment = comment;
	}

	public String getFromDate() {
		return fromDate;
	}

	public void setFromDate(String fromDate) {
		this.fromDate = fromDate;
	}

	public String getToDate() {
		return toDate;
	}

	public void setToDate(String toDate) {
		this.toDate = toDate;
	}
}
